--    Program: 		CAPAREXT_PAYMENT_METHOD.sql
--    Description:  Extract AR Payment Method Account information
--    Revision:     1st     
--    Parameter:    1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
  l_count				   	number;
  l_processed_count 		number;
  l_rejected_count  		number;
  l_err_msg       			varchar2(2000);
  
  l_utl_file  	       		utl_file.file_type;
  l_filename 		    	varchar2(255);
  l_pathname  		       	varchar2(100);
  
  l_set_of_books_name	 	varchar2(150);
  l_set_of_books_id		 	number;

  l_application_id       	number(15);
	
  cursor cur_armaa (p_set_of_books_id number) is
	select armaa.*
	from   AR_RECEIPT_METHOD_ACCOUNTS_ALL ARMAA,
 	       hr_organization_information hoi
	where  hoi.org_information_context = 'Operating Unit Information'
	and    hoi.org_information3 = to_char(p_set_of_books_id)
	and    hoi.organization_id = armaa.org_id;
  
begin
  l_pathname := '/tmp';
  l_filename := 'CAPAR_PAYMENT_METHOD.dat';
  
  l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w', 32767);
  
  l_count := 0;
  l_processed_count := 0;
                                           
  l_set_of_books_name := '&1';
  
  begin 
    select set_of_books_id
	 into   l_set_of_books_id
	 from   gl_sets_of_books
	 where  upper(short_name) = upper(l_set_of_books_name);
  exception when others then
    dbms_output.put_line(l_set_of_books_name||': set of books name not found');
    --raise;
  end;	
  
  for s_armaa in cur_armaa(l_set_of_books_id) loop        
        utl_file.put_line( l_utl_file, 
           s_armaa.BANK_ACCOUNT_ID                 || chr(9) ||
           s_armaa.RECEIPT_METHOD_ID               || chr(9) ||
           s_armaa.CASH_CCID                       || chr(9) ||
           s_armaa.CREATED_BY                      || chr(9) ||
           s_armaa.CREATION_DATE                   || chr(9) ||
           s_armaa.EARNED_CCID                     || chr(9) ||
           s_armaa.LAST_UPDATED_BY                 || chr(9) ||
           s_armaa.LAST_UPDATE_DATE                || chr(9) ||
           s_armaa.ON_ACCOUNT_CCID                 || chr(9) ||
           s_armaa.START_DATE                      || chr(9) ||
           s_armaa.UNAPPLIED_CCID                  || chr(9) ||
           s_armaa.UNEARNED_CCID                   || chr(9) ||
           s_armaa.UNIDENTIFIED_CCID               || chr(9) ||
           s_armaa.BANK_CHARGES_CCID               || chr(9) ||
           s_armaa.END_DATE                        || chr(9) ||
           s_armaa.FACTOR_CCID                     || chr(9) ||
           s_armaa.FACTOR_PRINT_PROGRAM_ID         || chr(9) ||
           s_armaa.FACTOR_TRANSMISSION_PROGRAM_ID  || chr(9) ||
           s_armaa.LAST_UPDATE_LOGIN               || chr(9) ||
           s_armaa.MIN_RECEIPT_AMOUNT              || chr(9) ||
           s_armaa.RECEIPT_CLEARING_CCID           || chr(9) ||
           s_armaa.REMITTANCE_CCID                 || chr(9) ||
           s_armaa.REMIT_PRINT_PROGRAM_ID          || chr(9) ||
           s_armaa.REMIT_TRANSMISSION_PROGRAM_ID   || chr(9) ||
           s_armaa.ATTRIBUTE_CATEGORY              || chr(9) ||
           s_armaa.ATTRIBUTE1                      || chr(9) ||
           s_armaa.ATTRIBUTE2                      || chr(9) ||
           s_armaa.ATTRIBUTE3                      || chr(9) ||
           s_armaa.ATTRIBUTE4                      || chr(9) ||
           s_armaa.ATTRIBUTE5                      || chr(9) ||
           s_armaa.ATTRIBUTE6                      || chr(9) ||
           s_armaa.ATTRIBUTE7                      || chr(9) ||
           s_armaa.ATTRIBUTE8                      || chr(9) ||
           s_armaa.ATTRIBUTE9                      || chr(9) ||
           s_armaa.ATTRIBUTE10                     || chr(9) ||
           s_armaa.ATTRIBUTE11                     || chr(9) ||
           s_armaa.ATTRIBUTE12                     || chr(9) ||
           s_armaa.ATTRIBUTE13                     || chr(9) ||
           s_armaa.ATTRIBUTE14                     || chr(9) ||
           s_armaa.ATTRIBUTE15                     || chr(9) ||
           s_armaa.PRIMARY_FLAG                    || chr(9) ||
           s_armaa.CLEARING_DAYS                   || chr(9) ||
           s_armaa.SHORT_TERM_DEBT_CCID            || chr(9) ||
           s_armaa.OVERRIDE_REMIT_ACCOUNT_FLAG     || chr(9) ||
           s_armaa.RISK_ELIMINATION_DAYS           || chr(9) ||
           s_armaa.ORG_ID                          || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE1               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE2               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE3               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE4               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE5               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE6               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE7               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE8               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE9               || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE10              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE11              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE12              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE13              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE14              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE15              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE16              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE17              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE18              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE19              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE20              || chr(9) ||
           s_armaa.GLOBAL_ATTRIBUTE_CATEGORY       || chr(9) ||
           s_armaa.EDISC_RECEIVABLES_TRX_ID        || chr(9) ||
           s_armaa.UNEDISC_RECEIVABLES_TRX_ID      || chr(9) ||
           s_armaa.BR_COLLECTION_DAYS              || chr(9) ||
           s_armaa.BR_STD_RECEIVABLES_TRX_ID       || chr(9) ||
           s_armaa.BR_REMITTANCE_CCID              || chr(9) ||
           s_armaa.BR_FACTOR_CCID
        );
        
        l_processed_count := l_processed_count + 1;
  end loop;  
 
  l_err_msg := '' ;
  if l_processed_count = 0 then
        l_rejected_count := l_rejected_count + 1;
        l_err_msg := l_set_of_books_name||': Payment Method Account not found';
  end if;
  
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPAREXT_PAYMENT_METHOD', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
  
  commit;
  
  utl_file.fclose(l_utl_file);
  
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit;
